/*
* Conditions Of Use
*
* This software was developed by employees of the Sigmatrix(Beijing) Corporation.
* This software is provided by sigmatrix as a service and is expressly
* provided "AS IS."  Sigmatrix MAKES NO WARRANTY OF ANY KIND, EXPRESS, IMPLIED
* OR STATUTORY, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTY OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NON-INFRINGEMENT
* AND DATA ACCURACY.  Sigmatrix does not warrant or make any representations
* regarding the use of the software or the results thereof, including but
* not limited to the correctness, accuracy, reliability or usefulness of
* the software.
*
* Permission to use this software is contingent upon your acceptance
* of the terms of this agreement.
*
*/
package com.sigmatrix.h5.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.alibaba.fastjson.JSONObject;
import com.sigmatrix.h5.dao.InStorageDao;
import com.sigmatrix.h5.dao.impl.common.impl.BaseDaoImpl;
import com.sigmatrix.h5.entity.InStorage;
import com.sigmatrix.h5.enums.InStorageStatusEnum;
import com.sigmatrix.h5.enums.OutStorageStatusEnum;
import com.sigmatrix.h5.enums.TableEnum;
import com.sigmatrix.h5.exception.DaoException;
import com.sigmatrix.h5.utils.CommonUtils;
import com.sigmatrix.h5.utils.Cons;
import com.sigmatrix.h5.utils.Utils;

/**
 * @ClassName: InStorageDaoImpl
 * @Description: 入库单数据库操作接口实现
 * @author youjun 
 * @date 2017-07-06 14:18:28
 */
@Repository
public class InStorageDaoImpl extends BaseDaoImpl<InStorage> implements InStorageDao {
	
	private static final String TABLE_NAME = "_dealers_in_storage";
	private static final Logger LOGGER = LoggerFactory.getLogger(InStorageDaoImpl.class);
	
	/**
	 * (non-Javadoc)
	 *@Title: update
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.InStorageDao#update(java.lang.String, java.lang.Integer, java.lang.Integer, java.lang.Integer, java.lang.Integer)
	 *@author 
	 *@date 2017年9月27日 下午5:58:23
	 */
	public int update(final String eseCode,final InStorage inStorage,final Integer actNum,final Integer actSpNum,final Integer status) throws DaoException{
		final StringBuilder sql = new StringBuilder()
				.append("UPDATE ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" SET _actual_number = ?,_actual_sp_num = ?,_status = ?,_in_storage_time = ?,_in_storage_type = ?");
				if(CommonUtils.isNotEmpty(inStorage.getLat()) && CommonUtils.isNotEmpty(inStorage.getLng()) ) {
					sql.append(" ,_lat = ?,_lng = ?, _province = ?, _city = ?,_county = ?,_address = ? ");
				}
				sql.append(" WHERE _id = ? ");
		Object[] params = new Object[]{actNum,actSpNum,status,inStorage.getInStorageTime(),inStorage.getInStorageType(),inStorage.getId()};
		int[] argTypes =  new int[]{Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.TIMESTAMP, Types.SMALLINT, Types.INTEGER}; 
		if(CommonUtils.isNotEmpty(inStorage.getLat()) && CommonUtils.isNotEmpty(inStorage.getLng()) ) {
			params = new Object[]{actNum,actSpNum,status,inStorage.getInStorageTime(),inStorage.getInStorageType(),inStorage.getLat(),inStorage.getLng(),inStorage.getProvince(),inStorage.getCity(),inStorage.getCounty(),inStorage.getAddress(),inStorage.getId()};
			argTypes =  new int[]{Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.TIMESTAMP, Types.SMALLINT,Types.VARCHAR,Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER,Types.NVARCHAR, Types.INTEGER}; 
		}
		LOGGER.info(sql.toString());
		LOGGER.info(JSONObject.toJSONString(params));
		int num = jdbcTemplate.update(sql.toString(), params, argTypes);
		return num;
		
	}
	

	@Override
	public int update(String eseCode, InStorage inStorage, Integer actNum, Integer actSpNum, Integer status,
			Integer planNum, Integer planSpNum) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("UPDATE ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" SET _actual_number = ?,_actual_sp_num = ?,_status = ?,_in_storage_time = ?,_in_storage_type = ?,_plan_number = ?,_plan_sp_num = ?")
				.append(" WHERE _id = ? ");
		final Object[] params = new Object[]{actNum,actSpNum,status,inStorage.getInStorageTime(),inStorage.getInStorageType(),inStorage.getPlanNumber(),inStorage.getPlanSpNum(),inStorage.getId()};
		final int[] argTypes =  new int[]{Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.TIMESTAMP, Types.SMALLINT, Types.INTEGER,Types.INTEGER, Types.INTEGER}; 
		LOGGER.info(sql.toString());
		LOGGER.info(JSONObject.toJSONString(params));
		int num = jdbcTemplate.update(sql.toString(), params, argTypes);
		return num;
	}

	/**
	 * (non-Javadoc)
	 *@Title: findById
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDao#findById(java.lang.Integer)
	 *@author youjun
	 *@date 2017年7月6日 下午3:47:21
	 */
	@Override
	public InStorage findById(final Long id, final String eseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE _id = ").append(id);
		LOGGER.info(sql.toString());
		final List<InStorage> list = jdbcTemplate.query(sql.toString(),new InStorageRowMapper());
		if (Utils.isEmpty(list)) {
			return null;
		}else{
			return list.get(0);
		}
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: findByOrderCode
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.InStorageDao#findByOrderCode(java.lang.String, java.lang.String)
	 *@author 
	 *@date 2017年9月27日 下午5:31:12
	 */
	@Override
	public InStorage findByOrderCode(final String orderCode, final String eseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE _order_code = '").append(orderCode).append("'");
		LOGGER.info(sql.toString());
		final List<InStorage> list = jdbcTemplate.query(sql.toString(),new InStorageRowMapper());
		if (Utils.isEmpty(list)) {
			return null;
		}else{
			return list.get(0);
		}
	}

	/**
	 * (non-Javadoc)
	 *@Title: save
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDao#save(com.sigmatrix.sc.BigDataInStorage.inter.entity.InStorage)
	 *@author youjun
	 *@date 2017年7月6日 下午3:47:17
	 */
	@Override
	public int save(final InStorage obj, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("INSERT INTO ")
				.append(getTname(enterpriseCode, TABLE_NAME))
				.append("(_order_code, _source_order_code, _creator_id, _create_time,")
				.append("_channel_type, _dealer_id, _status, _warehouse_id, ")
				.append("_plan_number, _plan_sp_num,_start_time, _end_time,")
				.append("_source,_remarks,_in_storage_type,_source_number,_actual_number,_actual_sp_num,_in_storage_time) ")
				.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?)");
		final Object[] params = new Object[] { 
				obj.getOrderCode(),obj.getSourceOrderCode(), obj.getCreatorId(), obj.getCreateTime(),
				obj.getChannelType(), obj.getDealerId(), obj.getStatus(),obj.getWarehouseId(), 
				obj.getPlanNumber(), obj.getPlanSpNum(),obj.getStartTime(), obj.getEndTime(),
				obj.getSource(), obj.getRemarks(),obj.getInStorageType(),obj.getSourceNumber(),obj.getActualNumber(),obj.getActualSpNum(),obj.getInStorageTime()
				};
		LOGGER.info(sql.toString());
		LOGGER.info(JSONObject.toJSONString(params));
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(final Connection con) throws SQLException {
				final PreparedStatement pstmt = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
				int index = 1;
				for (final Object value : params) {
					pstmt.setObject(index, value);
					index++;
				}
				return pstmt;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}


	/**(non-Javadoc)
	 *@Title: close
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDao#close(int, java.lang.String)
	 *@author youjun
	 *@date 2017年8月23日 下午5:18:23
	 */
	@Override
	public int close(final Long id, final String eseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("UPDATE ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" SET _status = ?, _update_time = ? WHERE _id = ?");
		final Object[] params = new Object[]{InStorageStatusEnum.CLOSE.getId(), new Date(), id};
		final int[] argTypes = new int[]{Types.SMALLINT, Types.TIMESTAMP, Types.INTEGER};
		LOGGER.info(sql.toString());
		LOGGER.info(JSONObject.toJSONString(params));
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: findByPage
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDao#findByPage(int, int, com.sigmatrix.sc.BigDataInStorage.inter.entity.InStorage)
	 *@author youjun
	 *@date 2017年7月6日 下午3:47:31
	 */
	@Override
	public List<InStorage> findByPage(final int pageNo, final int pageSize, final InStorage inStorage, 
			final Map<String, Object> otherParamMap, final String enterpriseCode) throws DaoException {
		try {
			final int startNo = (pageNo - 1) * pageSize + 1; // 当前起始条数
			final int endNo = pageNo * pageSize; // 当前结束条数
			final StringBuilder sql = new StringBuilder()
			.append("SELECT * FROM ")
			.append("(SELECT *, ROW_NUMBER() OVER(ORDER BY a._create_time DESC) AS RowNumber FROM ")
			.append(getTname(enterpriseCode, TABLE_NAME)).append(" as a ")
			.append("WHERE 1=1 ")
			.append(setWhereConditon(inStorage,"a.", otherParamMap))
			.append(") as b WHERE RowNumber BETWEEN ").append(startNo)
			.append(" AND ").append(endNo);
			LOGGER.info("分页查询={}",sql.toString());
			return jdbcTemplate.query(sql.toString(), new InStorageRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	@Override
	public List<InStorage> findByPage(int pageNo, int pageSize, InStorage inStorage, Map<String, Object> otherParamMap,
			String enterpriseCode, Integer terminalUserId) throws DaoException {
		try {
			final List<Object> paramList = new ArrayList<Object>();
			final int startNo = (pageNo - 1) * pageSize + 1; // 当前起始条数
			final int endNo = pageNo * pageSize; // 当前结束条数
			final StringBuilder sql = new StringBuilder();
			sql.append("SELECT * FROM ");
			sql.append("(SELECT a.*, ROW_NUMBER() OVER(ORDER BY a._create_time DESC) AS RowNumber FROM ");
			sql.append(getTname(enterpriseCode, TABLE_NAME)).append(" as a ");
			sql.append("LEFT JOIN ").append(getTname(enterpriseCode, TableEnum.DEALER_IN_STORAGE_TERMINAL_USER)).append(" ostu ");
			sql.append(" ON a._id = ostu._in_storage_id");
			sql.append(" WHERE a._status <> ?");
			paramList.add(Cons.STATUS_DELETE);
			sql.append(" AND a._dealer_id = ?");
			paramList.add(inStorage.getDealerId());
			sql.append(" AND (ostu._terminal_user_id = ? OR a._id NOT IN (SELECT _in_storage_id FROM ");
			sql.append(getTname(enterpriseCode, TableEnum.DEALER_IN_STORAGE_TERMINAL_USER)).append(" ))");
			paramList.add(terminalUserId);
			sql.append(setWhereConditon(inStorage,"a.", otherParamMap));
			sql.append(") as b WHERE RowNumber BETWEEN ").append(startNo);
			sql.append(" AND ").append(endNo);
			LOGGER.info("分页查询={}",sql.toString());
			final Object[] params = paramList.toArray();
			return jdbcTemplate.query(sql.toString(),params,new InStorageRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}

	public static void main(String[] args) {
		final StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM ");
		sql.append("(SELECT *, ROW_NUMBER() OVER(ORDER BY a._create_time DESC) AS RowNumber FROM ");
		sql.append("_XJYTL_dealers_in_storage").append(" as a ");
		sql.append("LEFT JOIN ").append("_XJYTL_dealers_in_storage_terminal_user").append(" ostu ");
		sql.append(" ON a._id = ostu._in_storage_id");
		sql.append(" WHERE a._status <> -1");
		sql.append(" AND a._dealer_id = 1");
		sql.append(" AND (ostu._terminal_user_id = 129 OR a._id NOT IN (SELECT _in_storage_id FROM ");
		sql.append("_XJYTL_dealers_in_storage_terminal_user").append(" ))");
		sql.append(" AND 1=1");
		sql.append(") as b WHERE RowNumber BETWEEN ").append(1);
		sql.append(" AND ").append(2);
		
		System.err.println(sql.toString());
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: getCountByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDao#getCountByCondition(com.sigmatrix.sc.BigDataInStorage.inter.entity.InStorage)
	 *@author youjun
	 *@date 2017年7月6日 下午3:47:36
	 */
	@Override
	public int getCountByCondition(final InStorage inStorage, final Map<String, Object> otherParamMap, final String enterpriseCode) throws DaoException {
		try {
			final StringBuilder sql = new StringBuilder()
					.append("SELECT COUNT(*) FROM ")
					.append(getTname(enterpriseCode, TABLE_NAME))
					.append(" WHERE 1=1")
					.append(setWhereConditon(inStorage,"", otherParamMap));
			LOGGER.info("分页查询统计总数={}",sql.toString());
			return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	/**
	 * (non-Javadoc)
	 *@Title: getCountByCondition
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.InStorageDao#getCountByCondition(com.sigmatrix.h5.entity.InStorage, java.util.Map, java.lang.String, java.lang.Integer)
	 *@author gaomingkai
	 *@date 2018年1月23日 上午10:33:08
	 */
	@Override
	public int getCountByCondition(InStorage inStorage, Map<String, Object> otherParamsMap, String enterpriseCode,
			Integer terminalUserId) throws DaoException {
		try {
			final List<Object> paramList = new ArrayList<Object>();
			final StringBuilder sql = new StringBuilder();
					sql.append("SELECT COUNT(a._id) FROM ");
					sql.append(getTname(enterpriseCode, TABLE_NAME)).append(" as a ");
					sql.append("LEFT JOIN ").append(getTname(enterpriseCode, TableEnum.DEALER_IN_STORAGE_TERMINAL_USER)).append(" ostu ");
					sql.append("ON a._id = ostu._in_storage_id ");
					sql.append("WHERE a._status <> ? ");
					paramList.add(Cons.STATUS_DELETE);
					sql.append("AND a._dealer_id = ? ");
					paramList.add(inStorage.getDealerId());
					sql.append(" AND (ostu._terminal_user_id = ? OR a._id NOT IN (SELECT _in_storage_id FROM ");
					sql.append(getTname(enterpriseCode, TableEnum.DEALER_IN_STORAGE_TERMINAL_USER)).append(" ))");
					paramList.add(terminalUserId);
					sql.append("AND 1=1");
					sql.append(setWhereConditon(inStorage,"", otherParamsMap));
			LOGGER.info("分页查询统计总数={}",sql.toString());
			return jdbcTemplate.queryForObject(sql.toString(), paramList.toArray(), Integer.class);
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}

	

	//设置分页查询条件
	private String setWhereConditon(final InStorage inStorage,
			final String aliasName, final Map<String, Object> otherParamMap) throws DaoException {
		StringBuilder whereSql = new StringBuilder()
		//.append(" AND ").append(aliasName).append("_dealer_id = ").append(inStorage.getDealerId())
		.append(" AND ").append(aliasName).append("_status in (")
		.append(InStorageStatusEnum.HAS_AUDIT.getId()).append(",")
		.append(InStorageStatusEnum.PROCEED_ING.getId()).append(",")
		.append(InStorageStatusEnum.COMPLETE.getId()).append(",")//已完成
		.append(InStorageStatusEnum.EXCEPTION.getId()).append(")");
		if(Utils.isNotEmpty(inStorage)) {
			if(Utils.isNotEmpty(inStorage.getChannelType())) {
				whereSql.append(" AND ").append(aliasName).append("_channel_type = ").append(inStorage.getChannelType());
			}
			if (Utils.isNotEmpty(inStorage.getOrderCode())) {
				whereSql.append(" AND ").append(aliasName).append("_order_code LIKE '%").append(inStorage.getOrderCode()).append("%'");
			}
		}
		if(!CollectionUtils.isEmpty(otherParamMap)) {
			final String createTimeStart = (String) otherParamMap.get("createTimeStart");
			final String createTimeEnd = (String) otherParamMap.get("createTimeEnd");
			if(StringUtils.isNotBlank(createTimeStart)) {
				whereSql.append(" AND ").append(aliasName).append("_create_time > '").append(createTimeStart).append("'");
			}
			if(StringUtils.isNotBlank(createTimeEnd)&&createTimeEnd.length()>=6) {
				whereSql.append(" AND ").append(aliasName).append("_create_time < '").append(createTimeEnd).append("'");
			}
		}
		return whereSql.toString();
	}


	/**
	 * (non-Javadoc)
	 *@Title: findListByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDao#findListByCondition(com.sigmatrix.sc.BigDataInStorage.inter.entity.InStorage)
	 *@author youjun
	 *@date 2017年7月6日 下午3:47:41
	 */
	@Override
	public List<InStorage> findListByCondition(final InStorage inStorage, final String enterpriseCode) throws DaoException {
		try {
			final StringBuilder sql = new StringBuilder()
					.append("SELECT * FROM ")
					.append(getTname(enterpriseCode, TABLE_NAME));
			final List<Object> paramList = new ArrayList<Object>();
			sql.append(" WHERE _status <> ?");
			paramList.add(Cons.STATUS_DELETE);
			sql.append(assemblyWhereConditon(inStorage, paramList, true, "", null));
			sql.append(" ORDER BY _create_time DESC");
			LOGGER.info(sql.toString());
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new InStorageRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: findForSync
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.InStorageDao#findForSync(java.lang.String, java.lang.Integer, java.lang.Short)
	 *@author 
	 *@date 2017年9月27日 下午5:53:20
	 */
	public List<InStorage> findForSync(final String eseCode,final Integer dealerId,final Short channlType) throws DaoException {
		try{
			final List<Object> paramList = new ArrayList<Object>();
			final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE _dealer_id = ").append(dealerId)
				.append(" AND _status in (")
				.append(InStorageStatusEnum.HAS_AUDIT.getId()).append(",")
				.append(InStorageStatusEnum.PROCEED_ING.getId()).append(",")
				.append(InStorageStatusEnum.EXCEPTION.getId()).append(")")
				.append(" AND _channel_type = ").append(channlType)
				.append(" ORDER BY _create_time DESC");
			LOGGER.info(sql.toString());
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new InStorageRowMapper());
		}catch(Exception e) {
			throw new DaoException(e);
		}
	}
	
	

	@Override
	public List<InStorage> findForSync(String eseCode, Integer dealerId, Short channlType, Integer terminalUserId)
			throws DaoException {
		try{
				final StringBuilder sql = new StringBuilder("SELECT ");
			    sql.append("os._id,os._order_code,os._source_order_code,os._customer_order_code,os._customer_type,os._customer_id,os._in_storage_time,os._dealer_id,os._products,");
			    sql.append("os._out_channel_id,os._other_type_id,os._plan_number,os._actual_number,os._actual_sp_num,os._plan_sp_num,os._ret_reason,os._is_auto_verify,os._creator_id,");
			    sql.append("os._create_time,os._update_time,os._update_id,os._enable_time,os._enable_id,os._disable_id,os._disable_time,os._audit_id,os._audit_time,os._channel_type,");
			    sql.append("os._source,os._status,os._terminal_id,os._warehouse_id,os._entry_warehouse_id,os._in_storage_type,os._remarks,os._end_time,os._start_time,os._fail_num,os._fail_sp_num,ostu._in_storage_id,ostu._terminal_user_id FROM ");
				sql.append(getTname(eseCode, TABLE_NAME)).append(" os ");
				sql.append("LEFT JOIN ").append(getTname(eseCode, TableEnum.DEALER_IN_STORAGE_TERMINAL_USER)).append(" ostu ");
				sql.append(" ON os._id = ostu._in_storage_id");
				final List<Object> paramList = new ArrayList<Object>();
				sql.append(" WHERE os._status <> ?");
				paramList.add(Cons.STATUS_DELETE);
				sql.append(" AND os._dealer_id = ?");
				paramList.add(dealerId);
				sql.append(" AND (ostu._terminal_user_id = ? OR os._id NOT IN (SELECT _in_storage_id FROM ");
				sql.append(getTname(eseCode, TableEnum.DEALER_IN_STORAGE_TERMINAL_USER)).append(" ))");
				paramList.add(terminalUserId);
				sql.append(" AND _status in (");
				sql.append(InStorageStatusEnum.HAS_AUDIT.getId()).append(",");
				sql.append(InStorageStatusEnum.PROCEED_ING.getId()).append(",");
				sql.append(InStorageStatusEnum.EXCEPTION.getId()).append(")");
				sql.append(" AND _channel_type = ").append(channlType);
				sql.append(" ORDER BY _create_time DESC");
			LOGGER.info(sql.toString());
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new InStorageRowMapper());
		}catch(Exception e) {
			throw new DaoException(e);
		}
	}
	
	
	/**
	 *@Title: assemblyWhereConditon
	 *@Description: 组装where条件部分
	 *@return String
	 *@author youjun
	 *@date 2017年7月7日 下午8:30:52
	 *@param inStorage
	 *@param paramList
	 *@param isExistWhereCondition
	 *@param alias 别名
	 * @param otherParamMap 
	 *@return
	 *@throws DaoException
	 */
	private String assemblyWhereConditon(final InStorage inStorage, final List<Object> paramList, 
			final boolean isExistWhereCondition, final String alias, final Map<String, Object> otherParamMap) throws DaoException {
		
		String aliasName = ""; // 别名+"."
		if(StringUtils.isNotBlank(alias)) {
			aliasName = new StringBuilder(alias).append(".").toString();
		}
		if(paramList==null) {
			throw new DaoException("参数列表不能为null");
		}
		StringBuilder whereSql = new StringBuilder();
		if(!isExistWhereCondition) {
			whereSql.append(" WHERE 1=1");
		}
		if(inStorage!=null) {
			if(inStorage.getChannelType()!=null) {
				whereSql.append(" AND ").append(aliasName).append("_channel_type = ?");
				paramList.add(inStorage.getChannelType());
			}
			if (StringUtils.isNotBlank(inStorage.getOrderCode())) {
				whereSql.append(" AND ").append(aliasName).append("_order_code LIKE ?");
				paramList.add(new StringBuilder("%").append(inStorage.getOrderCode()).append("%"));
			}
			if(inStorage.getStatus()!=null){
				if(inStorage.getStatus().intValue()==OutStorageStatusEnum.SPECIAL_FOR_PDA_SYNC_LIST.getId()) {
					whereSql.append(" AND ").append(aliasName).append("_status in (");
					final List<Integer> specialForPdaSyncList = new ArrayList<Integer>();
					specialForPdaSyncList.add(InStorageStatusEnum.HAS_AUDIT.getId());
					specialForPdaSyncList.add(InStorageStatusEnum.PROCEED_ING.getId());
					specialForPdaSyncList.add(InStorageStatusEnum.EXCEPTION.getId());
					final StringBuilder statusIn = new StringBuilder();
					for(final Integer status:specialForPdaSyncList){
						statusIn.append("?").append(",");
						paramList.add(status);
					}
					whereSql.append(statusIn.substring(0, statusIn.length()-1));
					whereSql.append(")");
				}else{
					whereSql.append(" AND ").append(aliasName).append("_status = ?");
					paramList.add(inStorage.getStatus());
				}
			}
		}
		if(!CollectionUtils.isEmpty(otherParamMap)) {
			final String createTimeStart = (String) otherParamMap.get("createTimeStart");
			final String createTimeEnd = (String) otherParamMap.get("createTimeEnd");
			if(StringUtils.isNotBlank(createTimeStart)) {
				whereSql.append(" AND ").append(aliasName).append("_create_time > ?");
				paramList.add(createTimeStart);
			}
			if(StringUtils.isNotBlank(createTimeEnd)&&createTimeEnd.length()>=6) {
				whereSql.append(" AND ").append(aliasName).append("_create_time < ?");
				paramList.add(createTimeEnd);
			}
		}
		return whereSql.toString();
	}

	/**
	 * @ClassName: InStorageRowMapper
	 * @Description: 封装返回的实体
	 * @author youjun 
	 * @date 2017-07-06 14:18:28
	 */
	public class InStorageRowMapper implements RowMapper<InStorage> {
		public InStorage mapRow(final ResultSet rs, final int rowNum) throws SQLException {
			final InStorage obj = new InStorage();
	        obj.setId(rs.getLong("_id"));
	        obj.setOrderCode(rs.getString("_order_code"));
	        obj.setSourceOrderCode(rs.getString("_source_order_code"));
	        obj.setCustomerOrderCode(rs.getString("_customer_order_code"));
	        obj.setCustomerType(rs.getShort("_customer_type"));
	        obj.setCustomerId(rs.getInt("_customer_id"));
	        obj.setInStorageTime(rs.getTimestamp("_in_storage_time"));
	        obj.setDealerId(rs.getInt("_dealer_id"));
	        obj.setProducts(rs.getString("_products"));
	        obj.setOutChannelId(rs.getInt("_out_channel_id"));
	        obj.setOtherTypeId(rs.getInt("_other_type_id"));
	        obj.setPlanNumber(rs.getInt("_plan_number"));
	        obj.setActualNumber(rs.getInt("_actual_number"));
	        obj.setPlanSpNum(rs.getInt("_plan_sp_num"));
	        obj.setActualSpNum(rs.getInt("_actual_sp_num"));
	        obj.setRetReason(rs.getString("_ret_reason"));
	        obj.setIsAutoVerify(rs.getShort("_is_auto_verify"));
	        obj.setCreatorId(rs.getInt("_creator_id"));
	        obj.setCreateTime(rs.getTimestamp("_create_time"));
	        obj.setUpdateId(rs.getInt("_update_id"));
	        obj.setUpdateTime(rs.getTimestamp("_update_time"));
	        obj.setEnableId(rs.getInt("_enable_id"));
	        obj.setEnableTime(rs.getTimestamp("_enable_time"));
	        obj.setDisableId(rs.getInt("_disable_id"));
	        obj.setDisableTime(rs.getTimestamp("_disable_time"));
	        obj.setAuditId(rs.getInt("_audit_id"));
	        obj.setAuditTime(rs.getTimestamp("_audit_time"));
	        obj.setChannelType(rs.getShort("_channel_type"));
	        obj.setSource(rs.getString("_source"));
	        obj.setStatus(rs.getShort("_status"));
	        obj.setTerminalId(rs.getInt("_terminal_id"));
	        obj.setWarehouseId(rs.getInt("_warehouse_id"));
	        obj.setEntryWarehouseId(rs.getInt("_entry_warehouse_id"));
	        obj.setInStorageType(rs.getShort("_in_storage_type"));
	        obj.setRemarks(rs.getString("_remarks"));
	        obj.setEndTime(rs.getTimestamp("_end_time"));
	        obj.setStartTime(rs.getTimestamp("_start_time"));
	        obj.setFailNum(rs.getInt("_fail_num"));
	        obj.setFailSpNum(rs.getInt("_fail_sp_num"));
	        return obj; 
		}
	}





}
